package com.es.pay.mailAndMsg;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.util.db.Dao;

public class MailAndMsgBiz {
	
	@SuppressWarnings("unchecked")
	public ArrayList<HrmCtmBean> getMenuAllList(String yyyymm)throws Exception {

		Dao dao = new Dao();
		HrmCtmBean menu = null;
	    ArrayList<HrmCtmBean> menuList = new ArrayList<HrmCtmBean>();
	    ArrayList<String> queryParm = new ArrayList<String>();
	    queryParm.add(yyyymm);
	    queryParm.add(yyyymm);
	    String sql = "select distinct ltrim(rtrim(salary.area_value+salary.rtm_code)) pid ,ltrim(rtrim(salary.ctm_code)) id, salary.ctm_name name from hrm_salary salary  where yyyy_mm=? "
	    			+" union all "
	    			+" select distinct 'a0' pid,ltrim(rtrim(salary.area_value+salary.rtm_code)) id , salary.area_value+'-'+salary.rtm_name name from hrm_salary salary where yyyy_mm=? order by 1";
	    try {
	    	ResultSet rs = dao.Select(sql,queryParm);
	      while (rs.next()){
	    	menu = new HrmCtmBean();
	        menu.setId(rs.getString("id"));
	        menu.setPid(rs.getString("pid"));
	        menu.setName(rs.getString("name"));
	        menuList.add(menu);
	      }
	    }
	    finally {
	    	dao.close();
	    }
	   /* if(menuList.size()==0)
	    	return null;*/
	    return menuList;
	}
	
	public ArrayList<HashMap<String,String>> getYYYYList() throws Exception{
		Dao dao = new Dao();
		ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
		try {
			String sql = "select distinct yyyy_mm code,yyyy_mm value  from hrm_salary  order by 1desc ";
			ResultSet resultSet = dao.Select(sql);
			while(resultSet.next()){
					HashMap<String,String> map = new HashMap<String,String>();
					map.put("code", resultSet.getString("code"));
					map.put("value", resultSet.getString("value"));
				  list.add(map);
			  }
		} finally{
			dao.close();
		}
		return list;
	}
	
	/**
	 * 获取邮件and 电话地址
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String,String>> getMailList(String yyyymm) throws Exception{
		Dao dao = new Dao();
		ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
		ArrayList<String> queryParm = new ArrayList<String>();
	    queryParm.add(yyyymm);
		try {
			String sql = "select  emp_code,emp_name,emp_mail,emp_phone_mobile,emp_role,'您本月实发工资为'+(select real_salary from hrm_salary where ctm_code = emp_code)+'元，详情请登陆系统或邮件查看' real_salary from hrm_emp_info where charindex(emp_code+',',?)>0 ";
			ResultSet resultSet = dao.Select(sql,queryParm);
			while(resultSet.next()){
				HashMap<String,String> map = new HashMap<String,String>();
				map.put("emp_code", resultSet.getString("emp_code"));
				map.put("emp_name", resultSet.getString("emp_name"));
				map.put("emp_mail", resultSet.getString("emp_mail"));
				map.put("emp_phone_mobile", resultSet.getString("emp_phone_mobile"));
				map.put("real_salary", resultSet.getString("real_salary"));
				list.add(map);
			  }
		} finally{
			dao.close();
		}
		return list;
	}
	
	/**
	 * 保存短信内容和结果
	 * @return
	 * @throws Exception
	 */
	public static void updateMsgResult(ArrayList<HashMap<String,String>> result) throws Exception{
		Dao dao = new Dao();
		ArrayList<ArrayList>queryParms = new ArrayList<ArrayList>();
		
		for(HashMap<String,String> map : result){
			ArrayList<String> list = new ArrayList<String>();
    		list.add(map.get("emp_code"));
    		list.add(map.get("emp_phone_mobile"));
    		list.add(map.get("content"));
    		list.add(map.get("msg_result"));
    		queryParms.add(list);
        }
		try {
			String sql = "insert into hrm_msg_history(emp_code ,phone ,msg, create_date,result	) " +
					"values(? ,? ,?, convert(varchar(10),getdate(),23),?)";
			dao.updateBatch(sql,queryParms);
		} catch(Exception e){
				System.out.print(e.getMessage());
		   }finally {		   
			   dao.close();
		    }
	}
	
	
}
